﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using BAL.Entities;
using DAL.SQLStatements;
using BAL.Utils;

namespace DAL.LogicComponents
{
    public class clsProductDAL
    {
        #region --- Product List By User
        public static DataTable queryGetProductListByUser(clsUser obj)
        {
            // Select all of products by user id
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblProduct as p, tblCountry as c, tblCity as ci, tblFuel as f, tblProducer as per";
            query.propColumn =    "p.colUserID, p.colEndDate, p.colTitle, p.colProductID, p.colProduct, p.colVisit, p.colInsertDate, p.colLastUpdate, p.colAvatar, p.colInsertDate, "
                                + "p.colAntiThiefDevice, p.colColor, p.IsNew, p.colPrice,ci.CityName, per.colProducer, p.colKilometersTraveled, "
                                + "p.colFuelID, p.colIsTrash, c.Country, f.Fuel, f.[colDescription]";
            query.propWhere = "p.colUserID= "+ obj.UserID +" and p.colIsDelete =1 and p.colFuelID = f.FuelID and p.colProducerID = per.colProducerID and p.colCityID= ci.CityID and c.CountryID= ci.CountryID";

            // return result
            return query.getDatatable();
        }
        #endregion ---

        #region --- Select Cart and Favorite List
        public static DataTable querySelectCartOrFavoriteList(clsProduct obj)
        {
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblProduct as p, tblCity as ci,tblCountry as co, tblUser as ur";
            query.propColumn = "*";
            query.propWhere = "p.colIsDelete = 1 and p.colCityID= ci.CityID and ci.CountryID = co.CountryID and ur.colUserID=p.colUserID and p.colProductID in(" + obj.ListProductID + ")";

            return query.getDatatable();
        }
        #endregion ---

        #region --- Select Product List
        public static DataTable queryselectProductList()
        {
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblProduct as p, tblCity as ci, tblProducer as per, tblUser as ur";
            query.propColumn = "colProductID,colProduct,CityName,colFirstName,colLastName,colProducer";
            query.propWhere = "p.colIsDelete = 1 and p.colCityID= ci.CityID and p.colProducerID = per.colProducerID and ur.colUserID=p.colUserID";

            return query.getDatatable();
        }
        #endregion ---

        #region --- Select Product by id
        public static DataTable queryselectProduct(clsProduct obj)
        {
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblProduct as p, tblProducer as per, tblCity as ci, tblCountry as co, tblUser as u";
            query.propColumn = "*";
            query.propWhere = "colProductID = '"+obj.ProductID+"' and p.colProducerID = per.colProducerID and p.colCityID= ci.CityID and ci.CountryID= co.CountryID and p.colUserId= u.colUserID";

            return query.getDatatable();
        }
        #endregion ---

        #region --- Check Product by User
        public static DataTable queryCheckProductByUser(clsProduct product)
        {
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblProduct";
            query.propColumn = "colUserID";
            query.propWhere = "colProductID = '" + product.ProductID + "'";

            return query.getDatatable();
        }
        #endregion ---

        #region --- Add New Product
        public static bool queryAddNewProduct(clsProduct obj)
        {
            Dictionary<String, Object> data = new Dictionary<String, Object>();
            data.Add("colProduct", "N'"+obj.ProductName+"'");
            data.Add("colTitle", "N'" + obj.Title + "'");
            data.Add("colDescription", "N'" + obj.Content + "'");
            data.Add("colProductionYear",  obj.ProductionYear);
            data.Add("colCc",  obj.Cc);
            data.Add("colEndDate",  "'"+obj.EndDate+"'");
            data.Add("colAvatar", "'"+obj.Avatar+"'");
            data.Add("colUserID", "'"+obj.Userid+"'");
            data.Add("colAntiThiefDevice", "'"+obj.AntiThiefDevice+"'");
            data.Add("colColor", "N'"+obj.Color+"'");
            data.Add("colProducerID", "'"+obj.ProducerID+"'");
            data.Add("colPrice", "'"+obj.Price+"'");
            data.Add("colKilometersTraveled", "'"+obj.KilometersTraveled+"'");
            data.Add("colFuelID", "'"+obj.FuelID+"'");
            data.Add("colCityID", "'"+obj.CityID+"'");
            data.Add("IsNew", "'" + obj.IsNew + "'");
            data.Add("colLastUpdate", "'" +clsDateTimeSetting.UTCTime() + "'");
            data.Add("colInsertDate", "'" + clsDateTimeSetting.UTCTime() + "'");
            data.Add("colIsTrash", 1);
            data.Add("colVisit", 0);
            data.Add("colIsDelete", 1);

            clsQueryBuilder query = new clsQueryBuilder();

            // Execute query
            return query.executeInsertInto("tblProduct", data);
        }
        #endregion ---

        #region --- Update Product
        public static bool queryUpdateProductByUser(clsProduct obj)
        {
            Dictionary<String, Object> cols = new Dictionary<String, Object>();
            if (obj.Avatar != null)
            {
                cols.Add("colAvatar", obj.Avatar);
            }
                cols.Add("colProduct", obj.ProductName);
                cols.Add("colVisit", obj.Visit);
                cols.Add("colLastUpdate", clsDateTimeSetting.UTCTime());
                cols.Add("colUserID", obj.Userid);
                cols.Add("colAntiThiefDevice", obj.AntiThiefDevice);
                cols.Add("colColor", obj.Color);
                cols.Add("IsNew", obj.IsNew);
                cols.Add("colProducerID", obj.ProducerID);
                cols.Add("colPrice", obj.Price);
                cols.Add("colKilometersTraveled", obj.KilometersTraveled);
                cols.Add("colFuelID", obj.FuelID);
                cols.Add("colCityID", obj.CityID);
            // Execute update query
            clsQueryBuilder query = new clsQueryBuilder();
            return query.executeUpdate("tblProduct", cols, String.Format("colProductID = {0}", obj.ProductID));

        }
        #endregion ---

        #region --- Delete Product
        public static bool queryDeleteProductByUser(clsProduct obj)
        {   
            Dictionary<String, Object> data = new Dictionary<String, Object>();
            data.Add("colIsDelete", 0);
            string where = "colProductID='" + obj.ProductID + "'";
            
            // New update query
            clsQueryBuilder query = new clsQueryBuilder();

            // Execute query
            return query.executeUpdate("tblProduct", data, where);
        }
        #endregion ---
    }
        
}